package com.basic.common;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
/**
 * EXCEL 文件读取工具
 * @author lihao
 *
 */
public class ExcelTool {
	public static final short COLOR_RED = -1;
	public static final short COLOR_BLUE = -2;
	public static final short COLOR_BLACK = -3;
	public static final short COLOR_GREEN = -4;
	public static final short COLOR_YELLOW = -5;
	public static final short COLOR_PINK = -6;
	public static final short COLOR_VIOLET = -7; 
	public static final short COLOR_WHITE = -8; 
	
	private  InputStream stream = null;
	private Workbook wb = null;
	private Sheet currentSheet = null;
	private boolean haveChange = false;
	private ExcelTool(){
		
	}
	 
	public static ExcelTool init(String filePath){
		try {
			ExcelTool ert = new ExcelTool();
			InputStream inp = new FileInputStream(filePath);
			ert.wb =WorkbookFactory.create(inp);
			inp.close();
			return ert;
		} catch (Exception e) {
			FuncStatic.errorTrace(e);
			return null;
		}
		
	}
	public static ExcelTool init(InputStream stream){
		try {
			ExcelTool ert = new ExcelTool();
			ert.stream = stream;
			ert.wb =WorkbookFactory.create(stream);
			return ert;
		} catch (Exception e) {
			FuncStatic.errorTrace(e);
			return null;
		}
	}
	public String[] getSheetNames(){
		ArrayList<String> list = new ArrayList<String>();
		int i=0;
		Sheet sheet = wb.getSheetAt(i);
		while(sheet != null){
			list.add(sheet.getSheetName());
			i++;
			try {
				sheet = wb.getSheetAt(i);
			} catch (Exception e) {
				sheet = null;
			}
			
		}
		String[] ss = new String[list.size()];
		ss = list.toArray(ss);
		return ss;
	}
	/**
	 * 进入第几个SHEET,从0开始
	 * @param sheetNum
	 */
	public void intoSheet(int sheetNum){
		currentSheet = wb.getSheetAt(sheetNum);
	}
	public void intoSheet(String sheetName){
		currentSheet = wb.getSheet(sheetName);
	}
	 
	public int getSheetRowNum(){ 
		return currentSheet.getLastRowNum();
	}
	
	/**
	 * 从 0 开始计数
	 * @param row 从 0 开始计数
	 * @param col 从 0 开始计数
	 * @return
	 */
	public String getContent(int row, int col){
		try {
			return currentSheet.getRow(row).getCell(col).getStringCellValue();
		} catch (Exception e) {
			return null;
		}
	}
	
	public String getContent(int row, char col){
		try {
			return currentSheet.getRow(row).getCell(getColumnByChar(col)).getStringCellValue();
		} catch (Exception e) {
			return null;
		}
	}
	
	public void setContent(int rownum , int col,String text){
		Row row = currentSheet.getRow(rownum);
		if(row == null){
			row = currentSheet.createRow(rownum);
		}
	    Cell cell = row.getCell(col);
	    if (cell == null){
	    	cell = row.createCell(col);
	    }
	    cell.setCellValue(text);    
	}
	public void setContent(int rownum , char col,String text){
		setContent(rownum,getColumnByChar(col),text);
	}
	
	public CellStyle createCellStyle(short fontColor, short foregroundColor){
		CellStyle style = wb.createCellStyle();
		style.setFillForegroundColor(getColor(foregroundColor));
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		Font f = wb.createFont();
	    f.setColor(getColor(fontColor));
	    style.setFont(f);
	    return style;
	}
	
	public void setCellStyle(int rownum , int col,CellStyle style){
		Row row = currentSheet.getRow(rownum);
		if(row == null){
			row = currentSheet.createRow(rownum);
		}
	    Cell cell = row.getCell(col);
	    if (cell == null){
	    	cell = row.createCell(col);
	    }
	    cell.setCellStyle(style);
	}
	public void setCellStyle(int rownum , char col,CellStyle style){
		setCellStyle(rownum,getColumnByChar(col),style);
	}
	
	
	private short getColor(short color){
		if(wb instanceof HSSFWorkbook){
			switch (color) {
			case ExcelTool.COLOR_RED :
				return new HSSFColor.RED().getIndex();
			case ExcelTool.COLOR_BLUE :
				return new HSSFColor.BLUE().getIndex();
			case ExcelTool.COLOR_GREEN :
				return new HSSFColor.GREEN().getIndex();
			case ExcelTool.COLOR_PINK :
				return new HSSFColor.PINK().getIndex();
			case ExcelTool.COLOR_BLACK :
				return new HSSFColor.BLACK().getIndex();
			case ExcelTool.COLOR_VIOLET :
				return new HSSFColor.VIOLET().getIndex();
			case ExcelTool.COLOR_YELLOW :
				return new HSSFColor.YELLOW().getIndex();
			case ExcelTool.COLOR_WHITE :
				return new HSSFColor.WHITE().getIndex();
			default:
				return new HSSFColor.BLACK().getIndex();
			}
		}
		else{
			switch (color) {
			case ExcelTool.COLOR_RED :
				return IndexedColors.RED.getIndex();
			case ExcelTool.COLOR_BLUE :
				return IndexedColors.BLUE.getIndex();
			case ExcelTool.COLOR_GREEN :
				return IndexedColors.GREEN.getIndex();
			case ExcelTool.COLOR_PINK :
				return IndexedColors.PINK.getIndex();
			case ExcelTool.COLOR_BLACK :
				return IndexedColors.BLACK.getIndex();
			case ExcelTool.COLOR_VIOLET :
				return IndexedColors.VIOLET.getIndex();
			case ExcelTool.COLOR_YELLOW :
				return IndexedColors.YELLOW.getIndex();
			case ExcelTool.COLOR_WHITE :
				return IndexedColors.WHITE.getIndex();
			default:
				return IndexedColors.BLACK.getIndex();
			}
		}
		
	}
	private int getColumnByChar(char c){ 
		return c - 'A';
	}
	public int getColumnNum(int row){
		try {
			return currentSheet.getRow(row).getLastCellNum();
		} catch (Exception e) {
			return 0;
		}
	}
	
	public void close(){
		if(stream != null){
			try {
				stream.close();
			} catch (Exception e) {
				FuncStatic.errorTrace(e);
			}
		}
	}
 
	public void save(OutputStream ops) throws Exception{
		wb.write(ops);
	}
	
	public void save(String filePath) throws Exception{
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
	}
	
	public static void main(String[] args) throws Exception {
//		ExcelTool ert = ExcelTool.init("c:/result_txt.xlsx"); 
//		ert.intoSheet(0);
//		CellStyle style1 = ert.createCellStyle(ExcelTool.COLOR_RED, ExcelTool.COLOR_WHITE);
//		CellStyle style2 = ert.createCellStyle(ExcelTool.COLOR_YELLOW, ExcelTool.COLOR_RED);
//		for(int i=0 ; i<1000; i++){
//			for(int x=0; x<10; x++){
//				ert.setContent(i, x, "test");
//				if(i % 2 ==0 ){
//					ert.setCellStyle(i, x, style1);
//				}else{
//					ert.setCellStyle(i, x, style2);
//				}
//				System.out.println(i+":"+x);
//			}
//		}
//		
//		ert.save("c:/result_txt_tmp.xlsx");
//		ert.close();
		
 
		
	}
}